/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import model.UserBean;


public class DBClass {
 public Connection createConnection() throws ClassNotFoundException,SQLException
    {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/rbpl", "root", "");
        return connection;
    }
 //we get values from servlet by passing bean object to insertdetails method
     public int insertDetails(UserBean bb) throws SQLException, ClassNotFoundException
     {
        try{
        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("insert into userdetails values(?,?,?,?,?,?,?)");
        //set values to prepared statement object by getting values from bean object
        pstmt.setString(1,bb.getNama());
        pstmt.setString(2,bb.getAlamat());
        pstmt.setString(3,bb.getNoTelp());
        pstmt.setString(4,bb.getJenisKelamin());
        pstmt.setString(5,bb.getEmail());
        pstmt.setString(6,bb.getUsername());
        pstmt.setString(7,bb.getPassword());
        int i = pstmt.executeUpdate();
        System.out.println("testing");
        return i;
        }
        catch(Exception e)
        {
            System.out.println(e);
            return 0;
        }
     }
   public List getAlldetails()throws SQLException, ClassNotFoundException
    {
       
        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("select * from userdetails");
        ResultSet rs = pstmt.executeQuery();
        List list = new ArrayList();
        while(rs.next())
        {
            UserBean ubean = new UserBean();
            ubean.setNama(rs.getString(1));
            ubean.setAlamat(rs.getString(2));
            ubean.setNoTelp(rs.getString(3));
            ubean.setJenisKelamin(rs.getString(4));
            ubean.setEmail(rs.getString(5));
            ubean.setUsername(rs.getString(6));
            ubean.setPassword(rs.getString(7));
            list.add(ubean);

        }
        return list;
    }
   public UserBean getDetails(String uname)throws SQLException, ClassNotFoundException
    {
       //here we will write code to get a single record from database
       Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("select * from userdetails where Username=?");
        pstmt.setString(1, uname);
        ResultSet rs = pstmt.executeQuery();
        
        UserBean ubean = new UserBean();
        while(rs.next())
        {
            
            ubean.setNama(rs.getString(1));
            ubean.setAlamat(rs.getString(2));
            ubean.setNoTelp(rs.getString(3));
            ubean.setJenisKelamin(rs.getString(4));
            ubean.setEmail(rs.getString(5));
            ubean.setUsername(rs.getString(6));
            ubean.setPassword(rs.getString(7));
        }
        ubean.setAction("update");
        return ubean;
    }
     public void UpateDetails(UserBean ubean, String name)throws SQLException, ClassNotFoundException
    {
         
        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("update userdetails set Nama=?, Alamat=?,NoTelp=?,JenisKelamin=?, Email=?,  Password=? where Username=? ");
        //set values to prepared statement object by getting values from bean object
        pstmt.setString(1,ubean.getNama());
        pstmt.setString(2,ubean.getAlamat());
        pstmt.setString(3,ubean.getNoTelp());
        pstmt.setString(4,ubean.getJenisKelamin());
        pstmt.setString(5,ubean.getEmail());

        pstmt.setString(6,ubean.getPassword());
        pstmt.setString(7,name);
        pstmt.executeUpdate();       
         
    }
      public void deleteDetails(String uname)throws SQLException, ClassNotFoundException
    {
       //here we will write code to get a single record from database
       Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("delete from userdetails where userName=?");
        pstmt.setString(1, uname);
        pstmt.executeUpdate();       
    }

}
